﻿package net.ojbk.saishi.dao;

import java.sql.ResultSet;

import java.sql.SQLException;
import java.sql.Statement;

import net.ojbk.database.DatabaseConnection;
import net.ojbk.saishi.po.syMatch;

public class syMatchDao {
	static final String tableName="syMatch";
	DatabaseConnection dbconn;
	Statement stmt;
	public syMatchDao(DatabaseConnection dbconn) {
		this.dbconn = dbconn;
		stmt=dbconn.getStatement();
	}
	
	public String getUserIdByMatchId(String matchId) {
		if (matchId == null || matchId.equals("null")) return "null";
		String flag = "null";
		ResultSet re;
		try {
			String sql="select * from "
					+ tableName
					+ " where matchId='"
					+ matchId
					+"';";
			re = stmt.executeQuery(sql);
			if (re.next()) {
				return re.getString("userId");
			}
		} catch(Exception e) {
			e.printStackTrace();
		}
		return flag;
		
	}
	
	/**
	 * 插入一条 赛事记录 将会用作赛事发布
	 * 
	 * @param sym Po层 syMatch 对象
	 * @return
	 */
	public boolean addOneRecord(syMatch sym){ // is OK? ：yes
		if (sym == null) return false; // sym 不能位空
		boolean flag=false;
		try {
			String sql="insert into "
					+ tableName
					+ " (matchId,userId,title,content,attachment,pw,beginTime,endTime,needFile,fileFormat,basicInfo,extraInfo) value('"
					+ sym.getMatchId()
					+ "','"
					+ sym.getUserId()
					+ "','"
					+ sym.getTitle()
					+ "','"
					+ sym.getContent()
					+ "','"
					+ sym.getAttachment()
					+ "','"
					+ sym.getPw()
					+ "','"
					+ sym.getBeginTime()
					+ "','"
					+ new java.sql.Timestamp(sym.getEndTime().getTime())
					+ "',"
					+ sym.getNeedFile()
					+ ",'"
					+ sym.getFileFormat()
					+ "','"
					+ sym.getBasicInfo()
					+ "','"
					+ sym.getExtraInfo()
					+ "');";
			System.out.println("endTime : \n" + new java.sql.Timestamp(sym.getBeginTime().getTime()));
			System.out.println("syMatchAdd : \n" + sql);
			stmt.executeUpdate(sql);
			flag=true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return flag;
	}
	
	/**
	 * 删除一条记录 将会用作撤销 赛事。
	 * mtachId 为主码
	 * 依照 sym.matchId 去 判断撤销
	 * 
	 * @param ud
	 * @return
	 */
	public boolean deleteOneRecord(syMatch sym) { // is OK? ：yes
		if (sym == null) return false;
		boolean flag = false;
		try {
			String sql = "DELETE FROM "
					+ tableName
					+ " WHERE "
					+ "matchId='"
					+ sym.getMatchId()
					+ "';";
			System.out.print(sql);
			stmt.executeUpdate(sql);
			flag = true;
		} catch(Exception e) {
			e.printStackTrace();
		}
		return flag;
	}
	
	/**
	 * 跟新一条记录 一般用于 重置比赛结束时间、赛事密码等。
	 * 1、传入 主码不能为空
	 * 
	 * @param sym
	 * @return
	 */
	public boolean updateOneRecord(syMatch sym) { // is OK? ：yes
		if (sym == null) return false;
		if (sym.getMatchId() == null) return false; // 主码不能为空
		boolean flag = false;
		try {
			// UPDATE userData SET id='10001', email='200562421@qq.com', tel='12345678901', idcard='362226199711200017', grade='2016', college='软件学院', clas='软件2016-2', registrationTime='2018.3.29', lastIP='192.168.1.1' WHERE id='10001';
			String sql = "UPDATE "
					+ tableName
					+ " SET "
					+ "userId='"
					+ sym.getUserId()
					+ "', title='"
					+ sym.getUserId()
					+ "', content='"
					+ sym.getContent()
					+ "', attachment='"
					+ sym.getAttachment()
					+ "', pw='"
					+ sym.getPw()
					+ "', beginTime='"
				    + sym.getBeginTime()
				    + "' , endTime='"
				    + sym.getEndTime()
				    + "' , needFile="
				    + sym.getNeedFile()
				    + " , fileFormat='"
				    + sym.getFileFormat()
				    + "', basicInfo='"
				    + sym.getBasicInfo()
				    + "', extraInfo='"
				    + sym.getExtraInfo()
					+ "' where matchId='"+sym.getMatchId()+"';";
			stmt.executeUpdate(sql);
			return true;
		} catch(Exception e) {
			e.printStackTrace();
		}
		return flag;
	}
	
	/**
	 * 查询一条数据
	 * 1、能够 根据 sym.matchId 查询
	 * 2、传入 主码为null 返回 false
	 * 3、传入 对象为null 返回 false
	 * 
	 * @param sym
	 * @return
	 */
	public boolean queryOneRecord(syMatch sym) { // is OK? ：yes
		if (sym == null) return false;
		if (sym.getMatchId() == null) return false; // 主码不能为空
		boolean flag = false;
		ResultSet re;
		try {
			//select * from userData where id='76670' or email='1531764720@qq.com'
			String sql="select * from "
					+ tableName
					+ " where matchId= '"
					+ sym.getMatchId()
					+ "';";
			re = stmt.executeQuery(sql);
			if (re.next()) {
				sym.setMatchId(re.getString("matchId"));
				sym.setUserId(re.getString("userId"));
				sym.setTitle(re.getString("title"));
				sym.setContent(re.getString("content"));
				sym.setAttachment(re.getString("attachment"));
				sym.setPw(re.getString("pw"));
				sym.setBeginTime(re.getTimestamp("beginTime"));
				sym.setEndTime(re.getTimestamp("endTime"));
				sym.setNeedFile(re.getBoolean("needFile"));
				sym.setFileFormat(re.getString("fileFormat"));
				sym.setBasicInfo(re.getString("basicInfo"));
				sym.setExtraInfo(re.getString("extraInfo"));
				flag = true;
			}
		} catch(Exception e) {
			e.printStackTrace();
		}
		return flag;
	}
	

	public int getCount() { // is OK? ：yes
		int count = 0;
		ResultSet re;
		try {
			// SELECT COUNT(*) FROM userData
			String sql="SELECT COUNT(*) FROM "
					+ tableName
					+ ";";
			re = stmt.executeQuery(sql);
			if (re.next()) {
				count = re.getInt(1);
			}
		} catch(Exception e) {
			e.printStackTrace();
		}
		return count;
	}
	
	public int getCountByUserId(String userId) { // is OK? ：yes
		int count = 0;
		ResultSet re;
		try {
			// SELECT COUNT(*) FROM userData
			String sql="SELECT COUNT(*) FROM "
					+ tableName
					+ " where userId='"
					+ userId
					+ "';";
			re = stmt.executeQuery(sql);
			if (re.next()) {
				count = re.getInt(1);
			}
		} catch(Exception e) {
			e.printStackTrace();
		}
		return count;
	}
	
	/**
	 * 如果 count=0 则 syms.lengh=0 不影响
	 * @return
	 */
	public syMatch[] queryAllRecod(){ // is OK? ：yes
		int count = this.getCount();
		syMatch[] syms=new syMatch[count]; 
		ResultSet re;
		syMatch sym;
		try {
			// SELECT * FROM userData
			String sql="SELECT * FROM "
					+ tableName
					+ ";";
			re = stmt.executeQuery(sql); 
			for (int i=0; re.next(); i++) {
				sym = new syMatch();
				sym.setMatchId(re.getString("matchId"));
				sym.setUserId(re.getString("userId"));
				sym.setTitle(re.getString("title"));
				sym.setContent(re.getString("content"));
				sym.setAttachment(re.getString("attachment"));
				sym.setPw(re.getString("pw"));
				sym.setBeginTime(re.getTimestamp("beginTime"));
				sym.setEndTime(re.getTimestamp("endTime"));
				sym.setNeedFile(re.getBoolean("needFile"));
				sym.setFileFormat(re.getString("fileFormat"));
				sym.setBasicInfo(re.getString("basicInfo"));
				sym.setExtraInfo(re.getString("extraInfo"));
				syms[i] = sym;
			}
		} catch(Exception e) {
			e.printStackTrace();
			return syms;
		}
		return syms;
	}
	
	public syMatch[] queryAllRecodByUserId(String userId){ // is OK? ：yes
		int count = this.getCountByUserId(userId);
		syMatch[] syms=new syMatch[count]; 
		ResultSet re;
		syMatch sym;
		try {
			// SELECT * FROM userData
			String sql="SELECT * FROM "
					+ tableName
					+ " where userId='"
					+ userId
					+ "' ;";
			re = stmt.executeQuery(sql); 
			for (int i=0; re.next(); i++) {
				sym = new syMatch();
				sym.setMatchId(re.getString("matchId"));
				sym.setUserId(re.getString("userId"));
				sym.setTitle(re.getString("title"));
				sym.setContent(re.getString("content"));
				sym.setAttachment(re.getString("attachment"));
				sym.setPw(re.getString("pw"));
				sym.setBeginTime(re.getTimestamp("beginTime"));
				sym.setEndTime(re.getTimestamp("endTime"));
				sym.setNeedFile(re.getBoolean("needFile"));
				sym.setFileFormat(re.getString("fileFormat"));
				sym.setBasicInfo(re.getString("basicInfo"));
				sym.setExtraInfo(re.getString("extraInfo"));
				syms[i] = sym;
			}
		} catch(Exception e) {
			e.printStackTrace();
			return syms;
		}
		return syms;
	}
	
	public boolean checkOneRecord(String matchId) { // is OK? ：yes
		boolean flag = false;
		ResultSet re;
		try {
			String sql="select * from "
					+ tableName
					+ " where matchId='"
					+ matchId
					+ "';";
			re = stmt.executeQuery(sql);
			if (re.next()) {
				flag = true;
			}
		} catch(Exception e) {
			e.printStackTrace();
		}
		return flag;
	}
    
	public static void main(String args[]) {
//		DatabaseConnection dbcon = new DatabaseConnection();
//		dbcon.close();
	}
}